package com.engine.board.biz.dataHadler;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.api.cube.util.CubeCipherUitl;
import com.engine.board.biz.filter.FilterParser;
import com.engine.board.biz.filter.FilterResult;
import com.engine.board.constant.Aggregator;
import com.engine.board.constant.ShowType;
import com.engine.board.entity.Widget;
import com.engine.board.entity.WidgetFilter;
import com.engine.board.util.BoardUtil;
import com.engine.board.util.DBUtil;
import com.engine.board.util.NumberUtil;
import com.engine.core.exception.ECException;
import com.engine.edc.util.EDCUtil;
import com.engine.edcreport.entity.ReportDataSet;
import weaver.common.DateUtil;
import weaver.general.Util;
import weaver.hrm.User;

import javax.servlet.http.HttpServletRequest;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;

public class DigitalPanelHandler extends WidgetHandler {
    private Map<String, ShowType> measuresShowType = new HashMap<>();

    private Map<String, String> subQuery = new HashMap<>();

    private List<String> measureColumnNames = new ArrayList<String>();

    private Map<String, Object> dataMap = new HashMap<String, Object>();

    public DigitalPanelHandler(Widget widget, User user, HttpServletRequest request) {
        super(widget, user, request);
    }

//    @Override
//    public Map<String, Object> getData() {
//        JSONObject config = JSONObject.parseObject(Util.null2String(widget.getConfig()));
//        if (BoardUtil.isNotEmpty(config) && config.containsKey("panel")) {
//            JSONObject panel = config.getJSONObject("panel");
//            ReportDataSet dataModel = reportDataSetService.dataSetInfo(widget.getDatamodel(), new HashMap<>());
//
//            if (BoardUtil.isEmpty(dataModel)) {
//                return new HashMap<>();
//            }
//
//            String datasource = "";
//            if ("DataBase".equals(dataModel.getConnectTemplate())) {
//                datasource = dataModel.getConnectTemplateId();
//            }
//
//            String selectSQL = "select ";
//            if (panel.containsKey("main")) { // 面板数据处理，只有一个
//                JSONObject measure = panel.getJSONObject("main");
//                String tableName = measure.getString("tableName").toUpperCase();
//                if (tableName.startsWith("(") && tableName.endsWith(")")) {
//                    subQuery.put(tableName, EDCUtil.genRandomCode(6));
//                    tableName = subQuery.get(tableName);
//                }
//
//                String fieldName = measure.getString("fieldName");
//                String shortKey = measure.getString("shortKey");
//                Aggregator aggregator = Aggregator.valueOf(Util.null2String(measure.getString("aggregator"), Aggregator.COUNT.name()));
//                ShowType showType = ShowType.valueOf(Util.null2s(measure.getString("showType"), ShowType.DEFAULT.name()));
//                selectSQL += aggregator.getSpliceSql(tableName + "." + fieldName, datasource) + " as " + shortKey + ",";
//
//                if (BoardUtil.isNotEmpty(showType) && showType != ShowType.DEFAULT) {
//                    measuresShowType.put(shortKey, showType);
//                }
//
//                measureColumnNames.add(shortKey);
//            }
//
//            selectSQL = selectSQL.substring(0, selectSQL.length() - 1);
//            String fromSQL = getFormSql(subQuery, dataModel);
//            String whereSQL = getWhereSql(subQuery, dataModel);
//            List<WidgetFilter> widgetFilters = new ArrayList<>();
//            if (panel.containsKey("main")) {
//                JSONObject measure = panel.getJSONObject("main");
//                if (measure.containsKey("filters")) {
//                    widgetFilters = JSON.parseArray(measure.getString("filters"), WidgetFilter.class);
//                }
//            }
//            FilterResult fieldFilter = FilterParser.parseFilters(widgetFilters);
//            String fieldSQL = whereSQL;
//            String filterSQL = fieldFilter.getSql().toUpperCase();
//            if (BoardUtil.isNotEmpty(filterSQL)) {
//                for (Map.Entry<String, String> entry : subQuery.entrySet()) {
//                    filterSQL = filterSQL.replace(entry.getKey(), entry.getValue());
//                }
//
//                if (BoardUtil.isNotEmpty(whereSQL.trim())) {
//                    fieldSQL = whereSQL + " and " + filterSQL;
//                } else {
//                    fieldSQL += " where " + filterSQL;
//                }
//            }
//
//            String sql = selectSQL + fromSQL + fieldSQL;
//
//            Map<String, Object> dataResult = new HashMap<String, Object>();
//
//            runSql(datasource, sql, fieldFilter.getParams());
//
//            String value = Util.null2String(dataMap.get(measureColumnNames.get(0)));
//            dataResult.put("main", value);
//
//            if (panel.containsKey("extra")) {
//                JSONArray extra = panel.getJSONArray("extra");
//                dataResult.put("extra", countPanel(panel, datasource, fromSQL, whereSQL, extra));
//            }
//
//            if (panel.containsKey("footer")) {
//                JSONArray footer = panel.getJSONArray("footer");
//                dataResult.put("footer", countPanel(panel, datasource, fromSQL, whereSQL, footer));
//            }
//            return dataResult;
//        } else {
//            return new HashMap<>();
//        }
//    }

    @SuppressWarnings("unchecked")
    private JSONArray countPanel(JSONObject panel, String datasource, String fromSQL, String whereSQL, JSONArray extra) {
        JSONArray result = new JSONArray();
        extra.forEach((e) -> {
            JSONObject countObj = (JSONObject) e;
            String select = getSelectSql(countObj, panel, datasource);

            if ("YOY,MOM".contains(countObj.getString("countType"))) {
                measuresShowType.put(measureColumnNames.get(0), ShowType.DEFAULT);
                List<String> conditions = getRatioWhereSql(countObj, panel, BoardUtil.isNotEmpty(whereSQL.trim()));

                runSql(datasource, select + fromSQL + whereSQL + conditions.get(0));
                String valueA = Util.null2String(dataMap.get(measureColumnNames.get(0)));

                runSql(datasource, select + fromSQL + whereSQL + conditions.get(1));
                String valueB = Util.null2String(dataMap.get(measureColumnNames.get(0)));
                result.add(NumberUtil.percentBigDecimal(new BigDecimal(valueA), new BigDecimal(valueB)));
            } else {
                List<WidgetFilter> widgetFilters = new ArrayList<>();
                if(countObj.containsKey("filters")){
                    widgetFilters = JSON.parseArray(countObj.getString("filters"), WidgetFilter.class);
                }

                FilterResult fieldFilter = FilterParser.parseFilters(widgetFilters);
                String fieldSQL = whereSQL;
                String filterSQL = fieldFilter.getSql().toUpperCase();
                if (BoardUtil.isNotEmpty(filterSQL)) {
                    for (Map.Entry<String, String> entry : subQuery.entrySet()) {
                        filterSQL = filterSQL.replace(entry.getKey(), entry.getValue());
                    }

                    if (BoardUtil.isNotEmpty(whereSQL.trim())) {
                        fieldSQL = whereSQL + " and " + filterSQL;
                    } else {
                        fieldSQL = " where " + filterSQL;
                    }
                }

                runSql(datasource, select + fromSQL + fieldSQL, fieldFilter.getParams());
                result.add(Util.null2String(dataMap.get(countObj.getString("shortKey"))));
            }
        });

        return result;
    }

    private String getSelectSql(JSONObject countObj, JSONObject panel, String datasource) {
        String selectSQL = "select ";
        if (panel.containsKey("main")) {
            JSONObject measure;
            Aggregator aggregator;
            ShowType showType;
            if ("YOY,MOM".contains(countObj.getString("countType"))) {
                measure = panel.getJSONObject("main");
                aggregator = Aggregator.valueOf(Util.null2s(measure.getString("aggregator"), Aggregator.SUM.name()));
                showType = ShowType.DEFAULT;
            } else {
                measure = countObj;
                aggregator = Aggregator.valueOf(Util.null2s(countObj.getString("aggregator"), Aggregator.AVG.name()));
                showType = ShowType.valueOf(Util.null2s(countObj.getString("showType"), ShowType.DEFAULT.name()));
            }

            String tableName = measure.getString("tableName").toUpperCase();
            if (tableName.startsWith("(") && tableName.endsWith(")")) {
                tableName = subQuery.get(tableName);
            }

            String fieldName = measure.getString("fieldName");
            String shortKey = measure.getString("shortKey");

            selectSQL += aggregator.getSpliceSql(tableName + "." + fieldName, datasource) + " as " + shortKey + ",";

            if (BoardUtil.isNotEmpty(showType) && showType != ShowType.DEFAULT) {
                measuresShowType.put(shortKey, showType);
            }
        }
        return selectSQL.substring(0, selectSQL.length() - 1);
    }


    /**
     * 获取同\环比sql条件
     *
     * @param countObj
     * @param panel
     * @param needAnd
     * @return
     */
    private List<String> getRatioWhereSql(JSONObject countObj, JSONObject panel, boolean needAnd) {
        List<String> sqlWhere = new ArrayList<>();
        if (panel.containsKey("main")) {
            String main = "";
            String extra = "";
            String countType = Util.null2String(countObj.getString("countType"));
            String ratioType = Util.null2String(countObj.getString("ratioType"));

            if ("YOY,MOM".contains(countType) && countObj.containsKey("timeField")) {
                main = needAnd ? " and " : " where ";
                extra = needAnd ? " and " : " where ";
                JSONObject timeFiled = countObj.getJSONObject("timeField");
                String tableName = timeFiled.getString("tableName").toUpperCase();
                if (tableName.startsWith("(") && tableName.endsWith(")")) {
                    tableName = subQuery.get(tableName);
                }

                String fieldName = timeFiled.getString("fieldName");
                String fieldRef = tableName + "." + fieldName;

                String baseDate = "";
                String baseFirstDay;
                Calendar calendar = Calendar.getInstance();
                String offsetDate = "";

                if ("YOY".equalsIgnoreCase(countType)) { // 同比
                    switch (ratioType) {
                        case "year":
                            baseDate = Util.null2s(countObj.getString("baseDate"), DateUtil.getYear());
                            baseFirstDay = DateUtil.getFirstDayOfYear(baseDate + "-01-01");
                            calendar.setTime(DateUtil.parseToDate(baseFirstDay));
                            calendar.add(Calendar.YEAR, -1);//当前时间减去一年，即一年前的时间    
                            offsetDate = DateUtil.getYear(calendar);
                            break;
                        case "month":
                            baseDate = Util.null2s(countObj.getString("baseDate"), DateUtil.getYear() + "-" + NumberUtil.add0(DateUtil.getMonth()));
                            baseFirstDay = DateUtil.getFirstDayOfMonth(baseDate + "-01");
                            calendar.setTime(DateUtil.parseToDate(baseFirstDay));
                            calendar.add(Calendar.YEAR, -1);

                            offsetDate = DateUtil.getYear(calendar) + "-" + NumberUtil.add0(DateUtil.getMonth(calendar));
                            break;
                        case "day":
                            baseDate = Util.null2s(countObj.getString("baseDate"), DateUtil.getCurrentDate());

                            calendar.setTime(DateUtil.parseToDate(baseDate));
                            calendar.add(Calendar.YEAR, -1);
                            offsetDate = DateUtil.getDate(calendar.getTime());
                            break;
                    }
                } else if ("MOM".equalsIgnoreCase(countType)) { // 环比
                    switch (ratioType) {
                        case "year":
                            baseDate = Util.null2s(countObj.getString("baseDate"), DateUtil.getYear());
                            baseFirstDay = DateUtil.getFirstDayOfYear(baseDate + "-01-01");
                            calendar.setTime(DateUtil.parseToDate(baseFirstDay));
                            calendar.add(Calendar.YEAR, -1);//当前时间减去一年    
                            offsetDate = DateUtil.getYear(calendar);
                            break;
                        case "month":
                            baseDate = Util.null2s(countObj.getString("baseDate"), DateUtil.getYear() + "-" + NumberUtil.add0(DateUtil.getMonth()));
                            baseFirstDay = DateUtil.getFirstDayOfMonth(baseDate + "-01");
                            calendar.setTime(DateUtil.parseToDate(baseFirstDay));
                            calendar.add(Calendar.MONTH, -1);//当前时间减去一月    
                            offsetDate = DateUtil.getYear(calendar) + "-" + NumberUtil.add0(DateUtil.getMonth(calendar));
                            break;
                        case "day":
                            baseDate = Util.null2s(countObj.getString("baseDate"), DateUtil.getCurrentDate());
                            calendar.setTime(DateUtil.parseToDate(baseDate));
                            calendar.add(Calendar.DATE, -1);//当前时间减去一天    
                            offsetDate = DateUtil.getDate(calendar.getTime());
                            break;
                    }
                }
                main += fieldRef + " like '" + baseDate + "%' ";
                extra += fieldRef + " like '" + offsetDate + "%' ";
            }
            sqlWhere.add(main);
            sqlWhere.add(extra);
        }
        return sqlWhere;
    }

    private void runSql(String datasource, String sql) {
        runSql(datasource, sql, new ArrayList<>());
    }

    private void runSql(String datasource, String sql, List<Object> fieldParams) {

        DBUtil.useConnection(datasource, (Connection conn) -> {
            PreparedStatement stmt = null;
            ResultSet rs = null;
            try {
                stmt = conn.prepareStatement(replaceDynamicVariables(sql));
                List<Object> params = new ArrayList<>();
                params.addAll(filterResult.getParams());
                params.addAll(fieldParams);

                for (int i = 0; i < params.size(); i++) {
                    stmt.setObject(i + 1, params.get(i));
                }

                rs = stmt.executeQuery();
                String dbName = Util.null2String(conn.getMetaData().getDatabaseProductName());
                boolean isMySQL = dbName.toUpperCase().contains("MYSQL");
                ResultSetMetaData rsMetaData = rs.getMetaData();
                int columnCount = rsMetaData.getColumnCount();
                String[] columnNames = new String[columnCount];
                for (int i = 0; i < columnCount; i++) {
                    if (isMySQL) {
                        columnNames[i] = rsMetaData.getColumnLabel(i + 1);
                    } else {
                        columnNames[i] = rsMetaData.getColumnName(i + 1);
                    }
                }

                while (rs.next()) {
                    for (String columnName : columnNames) {
                        String columnValue = CubeCipherUitl.decrypt(Util.null2String(rs.getString(columnName)));
                        String colName = columnName.toLowerCase();
                        if (measureColumnNames.contains(colName)) {
                            if ("".equals(columnValue)) {
                                columnValue = "0";
                            }
                            columnValue = new BigDecimal(columnValue).toPlainString();
                        }
                        if (measuresShowType.containsKey(colName)) {
                            columnValue = measuresShowType.get(colName).format(columnValue);
                        }
                        dataMap.put(colName, columnValue);
                    }

                }
            } catch (SQLException e) {
                BoardUtil.writeLog(this.getClass(), "Widget SQL : " + sql);
                throw new ECException(e.getMessage(), e);
            } finally {
                try {
                    if (rs != null)
                        rs.close();
                    if (stmt != null)
                        stmt.close();
                } catch (SQLException e) {
                    BoardUtil.writeLog(this.getClass(), e);
                }
            }
        });
    }
}
